from flask import Flask, render_template, request, jsonify
from db_utils import DBUtils
from charts import create_category_pie_chart, create_category_clicks_bar, create_words_recommendations_line, create_status_boxplot, create_books_clicks_ranking

app = Flask(__name__)

@app.route('/')
def index():
    # 创建数据库连接（使用环境变量配置）
    db = DBUtils(use_env=True)

    try:
        # 获取分页参数
        page = request.args.get('page', 1, type=int)
        per_page = 20  # 每页显示20条记录
        offset = (page - 1) * per_page

        # 获取搜索参数
        search = request.args.get('search', '')

        # 构建查询条件
        where_clause = ""
        params = []
        if search:
            where_clause = "WHERE book_name LIKE %s OR category LIKE %s"
            search_param = f"%{search}%"
            params = [search_param, search_param]

        # 查询总记录数
        count_sql = f"SELECT COUNT(*) as total FROM book_info {where_clause}"
        result = db.execute_query(count_sql, params, fetch_one=True)
        total = result['total'] if result else 0

        # 计算总页数
        total_pages = (total + per_page - 1) // per_page

        # 查询当前页的数据
        sql = f"""
            SELECT book_name, category, status, total_clicks, weekly_recommendations, 
                   total_words, total_recommendations, latest_chapter 
            FROM book_info {where_clause}
            ORDER BY total_clicks DESC 
            LIMIT %s OFFSET %s
        """
        params.extend([per_page, offset])
        books = db.execute_query(sql, params)

        return render_template('index.html',
                             books=books,
                             current_page=page,
                             total_pages=total_pages,
                             search=search)

    except Exception as e:
        import traceback
        print("Error occurred in index route:")
        traceback.print_exc()
        return render_template('index.html',
                             books=[],
                             current_page=1,
                             total_pages=1,
                             search='',
                             error=f"获取数据失败: {str(e)}")

    finally:
        db.close_connect()

@app.route('/api/category-distribution')
def get_category_distribution():
    """API endpoint for category distribution data"""
    db = DBUtils(use_env=False)
    try:
        # 查询各个类别的书籍数量
        sql = """
            SELECT category, COUNT(*) as count 
            FROM book_info 
            GROUP BY category 
            ORDER BY count DESC
        """
        result = db.execute_query(sql)

        # 转换为前端需要的格式
        data = [{"name": row['category'], "value": row['count']} for row in result]
        return jsonify(data)
    except Exception as e:
        return jsonify({"error": str(e)}), 500
    finally:
        db.close_connect()

@app.route('/api/category-clicks')
def get_category_clicks():
    """API endpoint for category clicks data"""
    db = DBUtils(use_env=False)
    try:
        # 查询各个类别的平均点击量
        sql = """
            SELECT 
                category,
                ROUND(AVG(total_clicks), 2) as avg_clicks
            FROM book_info 
            GROUP BY category 
            ORDER BY avg_clicks DESC
        """
        result = db.execute_query(sql)

        # 转换为前端需要的格式
        data = [{"name": row['category'], "value": float(row['avg_clicks'])} for row in result]
        return jsonify(data)
    except Exception as e:
        return jsonify({"error": str(e)}), 500
    finally:
        db.close_connect()

@app.route('/api/words-recommendations')
def get_words_recommendations():
    """API endpoint for words and recommendations data"""
    db = DBUtils(use_env=True)  # 使用环境变量配置
    try:
        # 查询书籍的字数和推荐数据
        sql = """
            SELECT 
                book_name,
                category,
                total_words,
                total_recommendations
            FROM book_info 
            WHERE total_words > 0 AND total_recommendations > 0
            ORDER BY total_words
        """
        result = db.execute_query(sql)
        
        # 打印调试信息
        print(f"查询到 {len(result)} 条记录")
        if len(result) > 0:
            print(f"第一条记录: {result[0]}")
            
        # 检查数据类型
        if len(result) > 0:
            first_row = result[0]
            print(f"数据类型检查:")
            print(f"  book_name: {type(first_row['book_name'])}")
            print(f"  category: {type(first_row['category'])}")
            print(f"  total_words: {type(first_row['total_words'])}")
            print(f"  total_recommendations: {type(first_row['total_recommendations'])}")

        # 转换为前端需要的格式，确保数值字段是浮点数
        data = [{
            "book_name": row['book_name'],
            "category": row['category'],
            "total_words": float(row['total_words']) if row['total_words'] is not None else 0,
            "total_recommendations": float(row['total_recommendations']) if row['total_recommendations'] is not None else 0
        } for row in result]
        
        # 再次检查转换后的数据
        if len(data) > 0:
            print(f"转换后第一条记录: {data[0]}")
            
        return jsonify(data)
    except Exception as e:
        import traceback
        print("获取字数和推荐数据时出错:")
        traceback.print_exc()
        return jsonify({"error": str(e)}), 500
    finally:
        db.close_connect()

@app.route('/charts')
def charts():
    try:
        return render_template('charts.html')
    except Exception as e:
        print(f"Error rendering charts page: {e}")
        return render_template('charts.html', error="加载页面失败")

@app.route('/line-chart')
def line_chart():
    try:
        return render_template('line_chart.html')
    except Exception as e:
        print(f"Error rendering line chart page: {e}")
        return render_template('line_chart.html', error="加载页面失败")

@app.route('/bar-chart')
def bar_chart():
    try:
        return render_template('bar_chart.html')
    except Exception as e:
        print(f"Error rendering bar chart page: {e}")
        return render_template('bar_chart.html', error="加载页面失败")

@app.route('/api/status-recommendations')
def get_status_recommendations():
    """API endpoint for status and recommendations boxplot data"""
    db = DBUtils(use_env=False)
    try:
        # 查询各状态下书籍的周推荐数
        sql = """
            SELECT 
                status,
                weekly_recommendations
            FROM book_info 
            WHERE weekly_recommendations > 0
            ORDER BY status
        """
        result = db.execute_query(sql)

        # 按状态分组数据
        data = {}
        for row in result:
            status = row['status']
            if status not in data:
                data[status] = []
            data[status].append(float(row['weekly_recommendations']))

        # 转换为前端需要的格式
        formatted_data = [{
            "status": status,
            "data": values
        } for status, values in data.items()]

        return jsonify(formatted_data)
    except Exception as e:
        return jsonify({"error": str(e)}), 500
    finally:
        db.close_connect()

@app.route('/api/books-ranking')
def get_books_ranking():
    """API endpoint for books ranking data"""
    db = DBUtils(use_env=False)
    try:
        # 查询点击量最高的前20本书
        sql = """
            SELECT 
                book_name,
                total_clicks,
                category
            FROM book_info 
            ORDER BY total_clicks DESC
            LIMIT 20
        """
        result = db.execute_query(sql)

        # 转换为前端需要的格式
        data = [{
            "name": row['book_name'],
            "value": float(row['total_clicks']),
            "category": row['category']
        } for row in result]

        return jsonify(data)
    except Exception as e:
        return jsonify({"error": str(e)}), 500
    finally:
        db.close_connect()

@app.route('/boxplot')
def boxplot():
    try:
        return render_template('boxplot.html')
    except Exception as e:
        print(f"Error rendering boxplot page: {e}")
        return render_template('boxplot.html', error="加载页面失败")

@app.route('/ranking-chart')
def ranking_chart():
    try:
        return render_template('ranking_chart.html')
    except Exception as e:
        print(f"Error rendering ranking chart page: {e}")
        return render_template('ranking_chart.html', error="加载页面失败")

if __name__ == '__main__':
    app.run(debug=True)
